package cake.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import cake.bean.Diy;
import cake.db.MySqlDb;
//DIY������ݿ���ʶ���
public class DiyDao {
private MySqlDb db=new MySqlDb();
	//ͨ��id��ȡ��������
	public Diy getDiy(long id)
	{
		Diy diy=null;
		String sql="select * from diy where id="+id;
		try {
			ResultSet rs = db.query(sql);
			while(rs.next())
			{
				diy=new Diy();
				diy.setAddress(rs.getString("address"));
				diy.setAmount(rs.getInt("amount"));
				diy.setCode(rs.getString("code"));
				diy.setId(rs.getLong("id"));
				diy.setPhone(rs.getString("phone"));
				diy.setRemark(rs.getString("remark"));
				diy.setStatus(rs.getInt("status"));
				diy.setUserId(rs.getLong("userid"));
				diy.setDescription(rs.getString("description"));
				diy.setPicture(rs.getString("picture"));
				diy.setQuantity(rs.getInt("quantity"));
				break;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			db.Close();
		}
		
		return diy;
	}
	//ͨ��Ż�ȡ��������
	public Diy getDiy(String code)
	{
		Diy diy=null;
		String sql="select * from diy where code='"+code+"'";
		try {
			ResultSet rs = db.query(sql);
			while(rs.next())
			{
				diy=new Diy();
				diy.setAddress(rs.getString("address"));
				diy.setAmount(rs.getInt("amount"));
				diy.setCode(rs.getString("code"));
				diy.setId(rs.getLong("id"));
				diy.setPhone(rs.getString("phone"));
				diy.setRemark(rs.getString("remark"));
				diy.setStatus(rs.getInt("status"));
				diy.setUserId(rs.getLong("userid"));
				diy.setDescription(rs.getString("description"));
				diy.setPicture(rs.getString("picture"));
				diy.setQuantity(rs.getInt("quantity"));
				break;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			db.Close();
		}
		
		return diy;
	}
	
	//��ҳ��ȡ�����б?��pageSizeΪ0ʱ��ʾ����ҳ
	public List<Diy> getDiys(int currPage,int pageSize,String where)
	{
		List<Diy> diys=new ArrayList<Diy>();
		String sql="select * from diy ";
		if(!where.equals(""))
		{
			sql+=" Where "+where;
		}
		if(pageSize!=0)
		{
			sql+=" limit "+((currPage-1)*pageSize)+","+pageSize;
		}
		
		try {
			ResultSet rs = db.query(sql);
			while(rs.next())
			{
				Diy diy=new Diy();
				diy.setAddress(rs.getString("address"));
				diy.setAmount(rs.getInt("amount"));
				diy.setCode(rs.getString("code"));
				diy.setId(rs.getLong("id"));
				diy.setPhone(rs.getString("phone"));
				diy.setRemark(rs.getString("remark"));
				diy.setStatus(rs.getInt("status"));
				diy.setUserId(rs.getLong("userid"));
				diy.setDescription(rs.getString("description"));
				diy.setPicture(rs.getString("picture"));
				diy.setQuantity(rs.getInt("quantity"));
				diys.add(diy);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally
		{
			db.Close();
		}
		
		return diys;
	}
	//��������
	public boolean addDiy(Diy diy)
	{
		diy.setStatus(0);
		Date d=new Date();
		DateFormat format = new SimpleDateFormat("yyyyMMddhhmmssS");
		//������ͨ��������ɣ���ȷ�����뱣֤�����ŵ�Ψһ��
		diy.setCode(format.format(d));
		String sql="Insert into Diy(address,amount,code,phone,remark,status,userid,description,picture,quantity) values('"+diy.getAddress()+"',"+diy.getAmount()+",'"+diy.getCode()+"','"+diy.getPhone()+"','"+diy.getRemark()+"',"+diy.getStatus()+","+diy.getUserId()+",'"+diy.getDescription()+"','"+diy.getPicture()+"',"+diy.getQuantity()+")";
		
		try
		{
			int row = db.execute(sql);
			
			return row>0?true:false;
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}finally
		{
			db.Close();
		}
		return false;
		
	}
	//�޸Ķ���
	public boolean updateDiy(Diy diy)
	{
		Diy oldDiy=getDiy(diy.getId());
		
		if(oldDiy==null)
		{
			return false;
		}
		
		String sql="Update Diy set address='"+diy.getAddress()+"',amount="+diy.getAmount()+",code='"+diy.getCode()+"',phone='"+diy.getPhone()+"',remark='"+diy.getRemark()+"',status="+diy.getStatus()+",userid="+diy.getUserId()+",description='"+diy.getDescription()+"',picture='"+diy.getPicture()+"',quantity="+diy.getQuantity()+" where id="+diy.getId();
		
		try
		{
			int row = db.execute(sql);
			
			return row>0?true:false;
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}finally
		{
			db.Close();
		}
		return false;
	}
	//ɾ��
	public  boolean deleteDiy(Diy diy)
	{
		Diy oldDiy=getDiy(diy.getId());
		
		if(oldDiy==null)
		{
			return false;
		}
		
		String sql="Delete from Diy where id="+diy.getId();
		
		try
		{
			int row = db.execute(sql);
			
			return row>0?true:false;
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}finally
		{
			db.Close();
		}
		return false;
	}
}
